package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dao.CustomerDAO;
import com.model.Customer;
import com.util.DB;

public class CustomerDAOImpl implements CustomerDAO {
	// 连接数据库
	// private static Connection conn = DB.getConnection();
	private Connection conn = DB.con;

	// add customer
	@Override
	public boolean addCustomer(Customer customer) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			st = conn
					.prepareStatement("insert into customer(CUSTOMERNO,CUSTOMERNAME,TELEPHONE,ADDRESS) values(?,?,?,?)");
			st.setString(1, customer.getCustomerNo());
			st.setString(2, customer.getCustomerName());
			st.setString(3, customer.getTelephone());
			st.setString(4, customer.getAddress());

			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	// delete customer
	@Override
	public boolean deleteCustomer(int id) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			st = conn.prepareStatement("delete from customer where id =?");
			st.setInt(1, id);
			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	// update customer
	@Override
	public boolean updateCustomer(Customer customer, int id) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		try {
			st = conn
					.prepareStatement("update customer set CUSTOMERNO = ?, CUSTOMERNAME = ?, TELEPHONE = ?, ADDRESS = ? where ID = ?");
			st.setString(1, customer.getCustomerNo());
			st.setString(2, customer.getCustomerName());
			st.setString(3, customer.getTelephone());
			st.setString(4, customer.getAddress());
			st.setInt(5, id);
			int num = st.executeUpdate();
			if (num > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return false;
	}

	// select customer
	@Override
	public List<Customer> selectCustomer(String key, String type, int page,
			int rows, String sort, String order) {
		// TODO Auto-generated method stub
		ArrayList<Customer> list = new ArrayList<Customer>();
		PreparedStatement st = null;
		int startRows = (page - 1) * rows;
		int endRows = rows;
		try {
			if (key != null) {
				String sql = "select * from customer where CUSTOMERNO like ? order by ID ASC limit ?,?";
				sql = sql.replace("CUSTOMERNO", type);
				sql = sql.replace("ID", sort);
				sql = sql.replace("ASC", order);
				st = conn.prepareStatement(sql);
				st.setString(1, "%" + key + "%");
				st.setInt(2, startRows);
				st.setInt(3, endRows);
				ResultSet rs = st.executeQuery();
				// 循环取出数据
				while (rs.next()) {
					Customer customer = new Customer();
					customer.setId(rs.getInt(1));
					customer.setCustomerNo(rs.getString(2));
					customer.setCustomerName(rs.getString(3));
					customer.setTelephone(rs.getString(4));
					customer.setAddress(rs.getString(5));
					list.add(customer);
				}
				return list;
			} else {
				String sql = "select * from customer order by CUSTOMERNO ASC limit ?,?";
				sql = sql.replace("CUSTOMERNO", sort);
				sql = sql.replace("ASC", order);
				st = conn.prepareStatement(sql);
				st.setInt(1, startRows);
				st.setInt(2, endRows);
				ResultSet rs = st.executeQuery();
				// 循环取出数据
				while (rs.next()) {
					Customer customer = new Customer();
					customer.setId(rs.getInt(1));
					customer.setCustomerNo(rs.getString(2));
					customer.setCustomerName(rs.getString(3));
					customer.setTelephone(rs.getString(4));
					customer.setAddress(rs.getString(5));
					list.add(customer);
				}
				return list;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DB.close(st, null);
		}
		return null;
	}

	// 获取customer数量
	public int getTotal() {
		PreparedStatement st = null;
		int count = 0;
		try {
			st = conn.prepareStatement("select id from customer");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	// 获取查询到的customer数量
	public int getLikeTotal(String key, String type) {
		PreparedStatement st = null;
		int count = 0;
		try {
			String sql = "select * from customer where CUSTOMERNO like ?";
			sql = sql.replace("CUSTOMERNO", type);
			st = conn.prepareStatement(sql);
			st.setString(1, "%" + key + "%");
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public <T> Customer getCustomer(T t) {
		// TODO Auto-generated method stub
		PreparedStatement st = null;
		Customer customer = null;
		try {
			if (t instanceof Integer) {
				st = conn
						.prepareStatement("select * from customer where id = ?");
				st.setInt(1, (int) t);
				ResultSet rs = st.executeQuery();
				while (rs.next()) {
					customer = new Customer();
					customer.setId(rs.getInt("ID"));
					customer.setCustomerNo(rs.getString("CUSTOMERNO"));
					customer.setCustomerName(rs.getString("CUSTOMERNAME"));
					customer.setTelephone(rs.getString("TELEPHONE"));
					customer.setAddress(rs.getString("ADDRESS"));
				}
				return customer;
			} else if (t instanceof String) {
				st = conn
						.prepareStatement("select * from customer where CustomerNo = ?");
				st.setString(1, (String) t);
				ResultSet rs = st.executeQuery();
				while (rs.next()) {
					customer = new Customer();
					customer.setId(rs.getInt("ID"));
					customer.setCustomerNo(rs.getString("CUSTOMERNO"));
					customer.setCustomerName(rs.getString("CUSTOMERNAME"));
					customer.setTelephone(rs.getString("TELEPHONE"));
					customer.setAddress(rs.getString("ADDRESS"));
				}
				return customer;
			} else {
				System.out.println("不支持该类型的搜索");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
